# -*- coding: UTF-8 -*-

import datetime
import sys
from sqlalchemy import and_,  func, or_
from typing import Dict, List, Any, Generator

from sqlalchemy import Boolean, Column, DateTime, Integer, String, Index, Table, ForeignKey, text, JSON, TEXT, DECIMAL, FLOAT
from sqlalchemy.orm import Session, relationship
from common.database import Base
from common.database import SessionMixin
from common.utils import is_empty
from core.logger import logger


__all__ = ['BillInstance']

class BillInstance(Base, SessionMixin):
    '''
    账单明细表->与费用中心-费用账单-账单明细账单一致=>统计项:实例 统计周期:账期 。
    '''
    __tablename__ = "bill_instance"
    bill_cycle = Column(String(64), nullable=False, comment='账期')
    cloud_platform = Column(String(64), nullable=False, comment='云平台')
    cost_unit = Column(String(128), nullable=False, comment='财务单元')
    account_id = Column(String(128), nullable=False, comment='账号ID')
    account_name = Column(String(128), nullable=False, comment='账号名称')
    owner_id = Column(String(128), nullable=False, comment='Owner账号ID')
    company = Column(String(128), nullable=False, index=True, comment='公司')
    bill_class = Column(String(128), nullable=False, index=True, comment='资产归类')
    environment = Column(String(128), nullable=False, index=True, comment='环境')
    maintain_department = Column(String(128), nullable=False, index=True, comment='维护部门')
    belong_department = Column(String(128), nullable=False, index=True, comment='归属部门')
    tag_key = Column(String(256), nullable=True, index=True, comment='标签名字')
    tag_value = Column(String(256), nullable=True, index=True, comment='标签值')
    product_code = Column(String(128), nullable=False, comment='产品Code')
    product_name = Column(String(128), nullable=False, comment='产品')
    commodity_code = Column(String(128), nullable=False, comment='产品明细Code')
    product_detail = Column(String(128), nullable=False, comment='产品明细')
    subscription_type = Column(String(128), nullable=False, comment='消费类型')
    service_period = Column(Integer, nullable=False, comment='服务时长')
    service_period_unit = Column(String(128), nullable=False, comment='时长单位')
    bill_type = Column(String(128), nullable=False, comment='账单类型')
    bill_method = Column(String(128), nullable=False, comment='账单计费方式')
    bill_item = Column(String(128), nullable=False, comment='账单计费项')
    instance_id = Column(String(128), nullable=False, comment='实例ID')
    instance_name = Column(String(256), nullable=False, comment='实例名称')
    resource_group = Column(String(128), nullable=False, comment='资源组')
    instance_tag = Column(String(512), nullable=False, comment='实例标签')
    instance_config = Column(String(1024), nullable=False, comment='实例配置')
    instance_spec = Column(String(256), nullable=False, comment='实例规格')
    internet_ip = Column(String(64), nullable=False, comment='公网IP')
    intranet_ip = Column(String(64), nullable=False, comment='私网IP')
    region = Column(String(128), nullable=False, comment='地域')
    zone = Column(String(128), nullable=False, comment='可用区')
    pretax_gross_amount = Column(DECIMAL(10,2), nullable=False, comment='原价')
    invoice_discount = Column(DECIMAL(10,2), nullable=False, comment='优惠金额')
    deducted_by_coupons = Column(DECIMAL(10,2), nullable=False, comment='优惠券抵扣')
    pretax_amount = Column(DECIMAL(10,2), nullable=False, comment='应付金额')
    payment_amount = Column(DECIMAL(10,2), nullable=False, comment='现金支付')
    deducted_by_cash_coupons = Column(DECIMAL(10,2), nullable=False, comment='代金券抵扣')
    deducted_by_prepaid_card = Column(DECIMAL(10,2), nullable=False, comment='储值卡支付金额')
    outstanding_amount = Column(DECIMAL(10,2), nullable=False, comment='欠费金额')
    currency = Column(String(32), nullable=False, comment='币种')
    comment = Column(String(256), nullable=True, comment='账单说明')

    __table_args__ = (
        Index('idx_bill_analysis_bill_cycle', 'bill_cycle', unique=False),
        {
            'mysql_engine': 'innodb',
            'mysql_charset': 'utf8mb4',
            'mysql_collate': 'utf8mb4_unicode_ci',
            'extend_existing': True,
            'mysql_row_format': 'dynamic'
        }
    )

    def __repr__(self):
        return f"<pid='{self.bill_cycle}', {self.product_detail}[{self.instance_id}], {self.instance_name}='{self.pretax_amount}'>"


    # 删除一个账期内所有的账单数据，重新同步账单时使用
    @classmethod
    def delete_cycle(cls, db: Session, bill_cycle: str):
        db.query(cls).filter(
            cls.bill_cycle == bill_cycle
        ).delete()
        db.commit()



    # @classmethod
    # def is_exist(cls, db: Session, bill_cycle: str, account_id: str, instance_id: str):
    #     """
    #     查找实例是否存在
    #     """
    #     result = db.query(cls).filter_by(
    #         bill_cycle = bill_cycle,
    #         account_id = account_id,
    #         instance_id = instance_id,
    #     ).first()
    #
    #     return bool(result)


    # @classmethod
    # def get_one_instance_bill(cls, db: Session, bill_cycle: str, account_id: str, instance_id: str):
    #     """
    #     获取某一个实例的账单信息
    #     """
    #     result = db.query(cls).filter_by(
    #         bill_cycle = bill_cycle,
    #         account_id = account_id,
    #         instance_id = instance_id,
    #     ).with_entities(
    #         cls.bill_cycle,
    #         cls.account_name,
    #         cls.instance_id,
    #         cls.instance_name,
    #         func.round(func.sum(BillInstance.pretax_amount),2).label('pretax_amount')
    #     ).group_by(cls.instance_id).to_dict()
    #
    #     return result


    @classmethod
    def get_all_instance_bill(cls, db: Session, bill_cycle: str):
        """
        获取账期内，所有的实例账单信息
        """
        result = db.query(cls).filter(
            cls.bill_cycle == bill_cycle
        ).with_entities(
            cls.bill_cycle,
            cls.cloud_platform,
            cls.account_name,
            cls.company,
            cls.bill_class,
            cls.environment,
            cls.maintain_department,
            cls.belong_department,
            cls.product_code,
            cls.product_name,
            cls.instance_id,
            cls.instance_name,
            cls.bill_item,
            cls.tag_key,
            cls.tag_value,
            cls.instance_tag,
            func.round(func.sum(BillInstance.pretax_amount),2).label('pretax_amount')
        ).group_by(cls.company, cls.account_name, cls.belong_department, cls.instance_id, cls.bill_item).to_dict()


        return result


    @classmethod
    def get_bill_cycles(cls, db: Session, page: int, per_page: int):
        """
        获取数据库中，已同步的账期
        """
        # 只获取账期字段去重的值
        result = db.query(cls).with_entities(cls.bill_cycle).distinct().paginate(page=page, per_page=per_page)

        return result


    #
    @classmethod
    def get_instance_sum_for_cycle_platform(cls,  db: Session, bill_cycle: str, cloud_platform: str):
        """
        统计账期内，某一个平台的总费用
        """
        result = db.query(cls).filter(
            cls.bill_cycle == bill_cycle,
            cls.cloud_platform == cloud_platform
        ).with_entities(
            cls.bill_cycle,
            cls.cloud_platform,
            func.round(func.sum(BillInstance.pretax_amount),2).label('pretax_amount'),
            func.round(func.sum(BillInstance.deducted_by_cash_coupons),2).label('deducted_by_cash_coupons'),
        ).to_dict()

        data = result[0]
        if not bool(data["bill_cycle"]):
            return False

        data["pay_amount"] = round(data["pretax_amount"] - data["deducted_by_cash_coupons"], 2)
        data["tax_amount"] = round(data["pay_amount"] / 1.06, 2)
        return data



    # 账期内, 根据field字段模糊查询value
    @classmethod
    def get_instance_search(cls, db: Session, bill_cycle: str, page: int, per_page: int, field: str, value: str):
        try:
            search_field = []
            display_field = cls.get_display_field()
            for key, col in cls.__table__.c.items():
                if is_empty(col.primary_key) and (field == "all" or key == field):
                    search_field.append(col.like(f"%{value}%"))

            result = db.query(cls).filter(
                cls.bill_cycle == bill_cycle,
                or_(*search_field)
            ).with_entities(
                *display_field
            ).paginate(page=page, per_page=per_page)
        except Exception as e:
            logger.exception(f'{sys._getframe().f_code.co_name} {e}')
            return None

        # 每个字段的说明
        result["fields"] = cls.get_display_field_meta()

        return result



    # 账期内, 根据field字段完全匹配value
    @classmethod
    def get_instance_match(cls, db: Session, bill_cycle: str, page: int, per_page: int, field: str, value: str):
        try:
            search_field = []
            display_field = cls.get_display_field()
            for key, col in cls.__table__.c.items():
                if key == field:
                    search_field.append(col == value)
                    break
            else:
                logger.error(f'field:{field} not in table')
                return None

            result = db.query(cls).filter(
                cls.bill_cycle == bill_cycle,
                *search_field
            ).with_entities(
                *display_field
            ).paginate(page=page, per_page=per_page)
        except Exception as e:
            logger.exception(f'{sys._getframe().f_code.co_name} {e}')
            return None

        # 每个字段的说明
        result["fields"] = cls.get_display_field_meta()

        return result


    @classmethod
    def get_instance_account(cls, db: Session, bill_cycle: str, page: int, per_page: int):
        """
        统计账期内，每个公司/每个账号的费用
        """
        result = db.query(cls).filter(
            cls.bill_cycle == bill_cycle
        ).with_entities(
            cls.bill_cycle,
            cls.company,
            cls.account_name,
            func.round(func.sum(BillInstance.pretax_amount),2).label('pretax_amount')
        ).group_by(cls.company, cls.account_name).paginate(page=page, per_page=per_page)

        result["fields"] = [
            {
                "field": "bill_cycle",
                "comment": "账期"
            },
            {
                "field": "company",
                "comment": "公司"
            },
            {
                "field": "account_name",
                "comment": "账号"
            },
            {
                "field": "pretax_amount",
                "comment": "金额"
            },
        ]
        return result

    # 账期内, 根据field字段聚合
    @classmethod
    def get_instance_aggregate(cls, db: Session, bill_cycle: str, field: str, page: int, per_page: int):
        try:
            display_field_meta = []
            display_field = [cls.bill_cycle]
            for key, col in cls.__table__.c.items():
                comment = col.comment.split(' ')[0]
                if key == field:
                    group_by_field = col
                    display_field.append(col)
                    display_field_meta.append({
                        "field": key,
                        "comment": comment
                    })
                    break
            else:
                logger.error(f'field:{field} not in table')
                return None

            result = db.query(cls).filter(
                cls.bill_cycle == bill_cycle
            ).with_entities(
                *display_field,
                func.round(func.sum(cls.pretax_amount), 2).label('pretax_amount')
            ).group_by(group_by_field).order_by("pretax_amount").paginate(page=page, per_page=per_page)
        except Exception as e:
            logger.exception(f'{sys._getframe().f_code.co_name} {e}')
            return None

        result["total_amount"] = 0
        for item in result["items"]:
            result["total_amount"] =  round(result["total_amount"] + item["pretax_amount"], 2)


        # 每个字段的说明
        result["fields"] = display_field_meta.extend([
                {
                    "field": "bill_cycle",
                    "comment": "账期"
                },
                {
                    "field": "pretax_amount",
                    "comment": "金额"
                }
            ])

        return result




    # 账期内, 分账类型的资产，根据所属部门聚合
    @classmethod
    def get_instance_cluster(cls, db: Session, bill_cycle: str, bill_class: str, page: int, per_page: int):
        try:
            result = db.query(cls).filter(
                cls.bill_cycle == bill_cycle,
                cls.bill_class == bill_class,
                cls.environment == "生产环境"
            ).with_entities(
                cls.bill_cycle,
                cls.bill_class,
                cls.belong_department,
                func.round(func.sum(BillInstance.pretax_amount), 2).label('pretax_amount')
            ).group_by(cls.belong_department).order_by("pretax_amount").paginate(page=page, per_page=per_page)
        except Exception as e:
            logger.exception(f'{sys._getframe().f_code.co_name} {e}')
            return None

        result["total_amount"] = 0
        for item in result["items"]:
            result["total_amount"] =  round(result["total_amount"] + item["pretax_amount"], 2)


        # 每个字段的说明
        result["fields"] = [
            {
                "field": "bill_cycle",
                "comment": "账期"
            },
            {
                "field": "belong_department",
                "comment": "所属部门"
            },
            {
                "field": "pretax_amount",
                "comment": "金额"
            }
        ]

        return result



    # 账期内, 分账类型的资产，一年每个月的费用
    @classmethod
    def get_instance_cluster_year(cls, db: Session, months: str, bill_class: str):
        try:
            result = db.query(cls).filter(
                cls.bill_cycle.in_(months),
                cls.bill_class == bill_class,
                cls.environment == "生产环境"
            ).with_entities(
                cls.bill_cycle,
                cls.bill_class,
                func.round(func.sum(BillInstance.pretax_amount), 2).label('pretax_amount')
            ).group_by(cls.bill_cycle).order_by(cls.bill_cycle).to_dict()
        except Exception as e:
            logger.exception(f'{sys._getframe().f_code.co_name} {e}')
            return None

        result = {
            "total_amount": 0,
            "items": result
        }
        for item in result["items"]:
            result["total_amount"] =  round(result["total_amount"] + item["pretax_amount"], 2)


        # 每个字段的说明
        result["fields"] = [
            {
                "field": "bill_cycle",
                "comment": "账期"
            },
            {
                "field": "bill_class",
                "comment": "资产归类"
            },
            {
                "field": "pretax_amount",
                "comment": "金额"
            }
        ]

        return result




    # 账期内, 根据环境分类，再按所属部门聚合
    @classmethod
    def get_instance_environment(cls, db: Session, bill_cycle: str, environment: str, page: int, per_page: int):
        try:
            result = db.query(cls).filter(
                cls.bill_cycle == bill_cycle,
                cls.environment == environment,
            ).with_entities(
                cls.bill_cycle,
                cls.environment,
                cls.belong_department,
                func.round(func.sum(BillInstance.pretax_amount), 2).label('pretax_amount')
            ).group_by(cls.belong_department).order_by("pretax_amount").paginate(page=page, per_page=per_page)
        except Exception as e:
            logger.exception(f'{sys._getframe().f_code.co_name} {e}')
            return None

        result["total_amount"] = 0
        for item in result["items"]:
            result["total_amount"] =  round(result["total_amount"] + item["pretax_amount"], 2)


        # 每个字段的说明
        result["fields"] = [
            {
                "field": "bill_cycle",
                "comment": "账期"
            },
            {
                "field": "belong_department",
                "comment": "所属部门"
            },
            {
                "field": "pretax_amount",
                "comment": "金额"
            }
        ]

        return result





    # 根据获取大数据集群的机器列表ip
    @classmethod
    def get_instance_ip_by_bigdata_cluster(cls, db: Session, bill_cycle: str, bill_class: str):
        try:
            # 实例的账单根据belong_department分开，商业智能在两个集群中都用，所以这里写死过滤
            result = db.query(cls).filter(
                cls.bill_cycle == bill_cycle,
                cls.bill_class == bill_class,
                cls.environment == "生产环境",
                cls.belong_department == "商业智能",
                cls.bill_item == "实例"
            ).with_entities(
                cls.bill_class,
                cls.instance_name,
                cls.intranet_ip,
                cls.instance_tag
            ).order_by(cls.instance_name).to_dict()
        except Exception as e:
            logger.exception(f'{sys._getframe().f_code.co_name} {e}')
            return None

        return result




    # 账期内, 根据环境聚合归类总费用
    @classmethod
    def get_instance_aggregate_class_env(cls, db: Session, bill_cycle: str):
        try:
            result = db.query(cls).filter(
                cls.bill_cycle == bill_cycle
            ).with_entities(
                cls.bill_cycle,
                cls.environment,
                cls.bill_class,
                func.round(func.sum(BillInstance.pretax_amount), 2).label('pretax_amount')
            ).group_by(cls.environment, cls.bill_class).order_by("pretax_amount").to_dict()
        except Exception as e:
            logger.exception(f'{sys._getframe().f_code.co_name} {e}')
            return None

        return result